﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataTier;
using System.Data.SqlClient;
using System.Data.Common;

namespace DbLayer
{
    public class DbPerson : DbObject, IDbPerson
    {

        private static DbPerson _instance;
        private IDbCity _dbCity = DbCity.Instance;

        private DbPerson()
        {
        }

        public static IDbPerson Instance
        {
            get
            {
                if (_instance == null)
                {
                    _instance = new DbPerson();
                }
                return _instance;
            }
        }

        public void AddCustomer(Customer customer)
        {

            int customerId = GetNextId("Customer", "id");
            int personId = GetNextId("Person", "id");

            string personInsert = BuildInsertStatement("Person", personId.ToString(), customer.FirstName, customer.LastName, customer.Address, customer.ZipCode.ToString(), customer.Email, customer.Phone);
            ExecuteStatement(personInsert);

            string customerInsert = BuildInsertStatement("Customer", customerId.ToString(), personId.ToString(), customer.Membership);
            ExecuteStatement(customerInsert);

        }

        public Customer GetCustomer(int id)
        {

            Customer customer = null;
            // string statement = BuildSelectStatement("Customer", "id ='" + id.ToString() + "'");
            // string statement = "SELECT * FROM Customer INNER JOIN (SELECT id as keyId, firstname, lastname, address, zipCode, email, phone from Person) p ON Customer.personId = p.keyId";

            string statement = @"SELECT c.*, p.*
                                FROM Customer c, Person p
                                WHERE c.id = " + id.ToString() + " AND c.personId = p.id";

            SqlDataReader reader = ExecuteQuery(statement);

            if (reader.Read())
            {
                customer = BuildCustomer(reader);
            }

            reader.Close();

            return customer;

        }

        public IList<Customer> GetAllCustomers()
        {

            List<Customer> customers = new List<Customer>();

//            string statement = @"SELECT
//                                c.*,
//                                p.address, p.email, p.firstname, p.id, p.lastname, p.phone,
//                                z.*
//                                FROM Customer c, Person p, ZipCodeCity z
//                                WHERE c.personId = p.id AND z.zipCode = p.zipCode";

            string statement = @"SELECT c.*, p.*
                                FROM Customer c, Person p
                                WHERE c.personId = p.id";

            SqlDataReader reader = ExecuteQuery(statement);

            while (reader.Read())
            {
                customers.Add(BuildCustomer(reader));
            }

            reader.Close();

            return customers;

        }

        public void AddEmployee(Employee employee)
        {

            int employeeId = GetNextId("Employee", "id");
            int personId = GetNextId("Customer", "id");

            string personInsert = BuildInsertStatement("Person", personId.ToString(), employee.FirstName, employee.LastName, employee.Address, employee.ZipCode.ToString(), employee.Email, employee.Phone);
            ExecuteStatement(personInsert);

            string employeeInsert = BuildInsertStatement("Employee", employeeId.ToString(), personId.ToString());
            ExecuteStatement(employeeInsert);

            //string statement = BuildInsertStatement("Employee", "1", employee.FirstName, employee.LastName, employee.Address, employee.ZipCode.ToString(), employee.Email, employee.Phone, employee.EmployeeId.ToString());
            //ExecuteStatement(statement);

        }

        public IList<Employee> GetAllEmployees()
        {

            List<Employee> employees = new List<Employee>();

            //string statement = "SELECT * FROM Employee INNER JOIN (SELECT id as keyId, firstname, lastname, address, zipCode, email, phone from Person) p ON Employee.personId = p.keyId";
            string statement = @"SELECT e.*, p.*
                                FROM Employee e, Person p
                                WHERE e.personId = p.id";
            SqlDataReader reader = ExecuteQuery(statement);

            while (reader.Read())
            {
                employees.Add(BuildEmployee(reader));
            }

            reader.Close();

            return employees;

        }

        public Employee GetEmployee(int id)
        {

            Employee employee = null;
            string statement = BuildSelectStatement("Employee", "id ='" + id.ToString() + "'");
            SqlDataReader reader = ExecuteQuery(statement);

            if (reader.Read())
            {
                employee = BuildEmployee(reader);
            }

            reader.Close();

            return employee;

        }

        private Customer BuildCustomer(SqlDataReader reader)
        {

            Customer customer = new Customer();

            customer.Address = reader["address"].ToString();
            customer.CustomerId = Convert.ToInt32(reader["id"]);
            customer.Email = reader["email"].ToString();
            customer.FirstName = reader["firstname"].ToString();
            customer.LastName = reader["lastname"].ToString();
            customer.Membership = reader["membership"].ToString();
            customer.Phone = reader["phone"].ToString();
            customer.ZipCode = Convert.ToInt32(reader["zipcode"].ToString());
            customer.City = _dbCity.GetCity(customer.ZipCode);
            //customer.City = reader["city"].ToString();

            return customer;

        }

        private Employee BuildEmployee(SqlDataReader reader)
        {

            Employee employee = new Employee();

            employee.FirstName = reader["firstname"].ToString();
            employee.LastName = reader["lastname"].ToString();
            employee.Address = reader["address"].ToString();
            employee.Email = reader["email"].ToString();
            employee.Phone = reader["phone"].ToString();
            employee.EmployeeId = Convert.ToInt32(reader["id"]);
            employee.ZipCode = Convert.ToInt32(reader["zipCode"]);
            employee.City = _dbCity.GetCity(employee.ZipCode);

            return employee;

        }

    }
}